﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace FeedData
{
    public class ExcelUtils
    {
        public static List<BaoGia> ExecuteExcel(string filePath)
        {
            List<BaoGia> lstProduct = new List<BaoGia>();

            if (filePath.Length > 0)
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
                {
                    Workbook workBook = document.WorkbookPart.Workbook;
                    IEnumerable<Sheet> workSheets = workBook.Descendants<Sheet>();
                    SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;

                    string sheetName = workSheets.First(s => s.Name == @"BaoGia").Id;

                    WorksheetPart productSheet = (WorksheetPart)document.WorkbookPart.GetPartById(sheetName);

                    lstProduct = GetDataExcel(productSheet.Worksheet, sharedStrings);

                }
            }


            return lstProduct;
        }

        public static List<BaoGia> GetDataExcel(Worksheet worksheet, SharedStringTable sharedString)
        {
            var result = new List<BaoGia>();

            IEnumerable<Row> dataRows =
                from row in worksheet.Descendants<Row>()
                where row.RowIndex > 1
                select row;

            var count = 0;

            DateTime time = DateTime.Now;

            foreach (Row row in dataRows)
            {
                count++;
                IEnumerable<string> textValues =
                    from cell in row.Descendants<Cell>()
                    select
                            (cell.CellValue != null) ?
                                ((cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString)
                                ? sharedString.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText
                                : cell.CellValue.InnerText)
                                : String.Empty;

                if (textValues.Count() > 0)
                {
                    try
                    {
                        var textArray = textValues.ToArray();

                        var o = new BaoGia();
                        o.ID = count +1;
                        o.HinhThucQuangCao = textArray[0];
                        o.TenSanPham = textArray[1];
                        o.DonViTinh = string.IsNullOrEmpty(textArray[2]) ? "Blank" : textArray[2];
                        o.Tag = string.IsNullOrEmpty(textArray[3]) ? "Blank" : textArray[3];
                        o.Website = string.IsNullOrEmpty(textArray[4]) ? "Blank" : textArray[4];
                        o.ChuyenMuc = string.IsNullOrEmpty(textArray[5]) ? "Blank" : textArray[5];
                        o.Banner = string.IsNullOrEmpty(textArray[6]) ? "Blank" : textArray[6];
                        o.DonGia = textArray[7];

                        if (!string.IsNullOrEmpty(textArray[8]))
                        {
                            time = DateTime.FromOADate(Convert.ToDouble(textArray[8]));
                            o.ThoiGianHieuLucBatDau = time.ToString();
                        }
                        else
                            o.ThoiGianHieuLucBatDau = string.Empty;

                        if (!string.IsNullOrEmpty(textArray[9]))
                        {
                            time = DateTime.FromOADate(Convert.ToDouble(textArray[9]));
                            o.ThoiGianHieuLucKetThuc = time.ToString();
                        }
                        else
                            o.ThoiGianHieuLucKetThuc = string.Empty;

                        result.Add(o);
                    }
                    catch (Exception ex)
                    {
                        CreateLogFiles Err = new CreateLogFiles();
                        Err.ErrorLog(ConfigurationManager.AppSettings["ErrorPath"], "Line" + count + " : " + ex.Message);
                    }
                }
                else
                    break;


            }
            return result;
        }


    }
}


public class CreateLogFiles
{
    private string sLogFormat;
    private string sErrorTime;

    public CreateLogFiles()
    {
        //sLogFormat used to create log files format :

        // dd/mm/yyyy hh:mm:ss AM/PM ==> Log Message

        sLogFormat = DateTime.Now.ToShortDateString().ToString() + " " + DateTime.Now.ToLongTimeString().ToString() + " ==> ";

        //this variable used to create log filename format "

        //for example filename : ErrorLogYYYYMMDD

        string sYear = DateTime.Now.Year.ToString();
        string sMonth = DateTime.Now.Month.ToString();
        string sDay = DateTime.Now.Day.ToString();
        sErrorTime = sYear + sMonth + sDay;
    }
    public void ErrorLog(string sPathName, string sErrMsg)
    {
        StreamWriter sw = new StreamWriter(sPathName + "\\Error_" + sErrorTime + ".txt", true);
        sw.WriteLine(sLogFormat + sErrMsg);
        sw.Flush();
        sw.Close();
    }
}


public class BaoGia
{
    public int ID { get; set; }
    public string HinhThucQuangCao { get; set; }
    public string TenSanPham { get; set; }
    public string DonViTinh { get; set; }
    public string Tag { get; set; }
    public string Website { get; set; }
    public string ChuyenMuc { get; set; }
    public string Banner { get; set; }
    public string DonGia { get; set; }
    public string ThoiGianHieuLucBatDau { get; set; }
    public string ThoiGianHieuLucKetThuc { get; set; }
}


public static class ExtensionMethods
{
    public static T ChangeType<T>(this object obj)
    {
        if (null == obj)
            return default(T);
        try
        {
            var t = (T)Convert.ChangeType(obj, typeof(T));
            return t;
        }
        catch
        {
            return default(T);
        }
    }
}